﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreDatabase_BackupSetID]
#-- Purpose:		Provides a mechanism to restore a database to a label
#--					via stored procedure call
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#--					dbo.spRestoreDatabase_Label
#--					dbo.spRestoreDatabase_PointInTime
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreDatabase_BackupSetID]
(
	@database_name		sysname,
	@last_backup_set_id	int,
	@restore_now		bit					= 0,
	@verify_only		bit					= 0,
	@destination_db		sysname				= NULL,
	@stopat_time		datetime			= NULL
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@sError varchar(1000), @sResults varchar(8000), @database sysname, @has_rights bit, @start_date datetime
DECLARE		@sCommand nvarchar(4000), @noarch bit
DECLARE		@db_id int, @db_diff_id int, @last_id int, @db_diff_backupsetid int
DECLARE		@id int, @first_id int, @physical_device_name varchar(4000), @replace bit, @recover bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spRestoreDatabase_BackupSetID'

--- Convert '' to NULL for @destination_db
IF LTRIM(RTRIM(@destination_db)) = ''
	SET	@destination_db = NULL

--- Destination Database
SELECT	@database = ISNULL(@destination_db, @database_name)

--- Determine if the user is authorized to do restores
EXEC dbo.spRestoreIsAllowed @database_name = @database, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0 AND ISNULL(@restore_now, 0) = 1
  BEGIN
	SELECT	@sError = 'You are not authorized to restore the database ' + @database
	RAISERROR (@sError, 16, 1)
  END

--- Don't allow restoring system databases or centraldba_db with this process
IF @database IN ('master', 'model', 'msdb', DB_NAME())
  BEGIN
	SELECT	@sError = 'This process cannot be used to restore the database ' + @database
	RAISERROR (@sError, 16, 1)
  END

--- Error if no backups with this label exist
IF NOT EXISTS (SELECT TOP 1 * FROM msdb.dbo.backupset WHERE database_name = @database_name AND backup_set_id <= @last_backup_set_id)
  BEGIN
	SELECT	@sError = 'The requested backup of ' + QUOTENAME(@database_name) + ' was not found with the id ' + QUOTENAME(CAST(@last_backup_set_id as varchar(100)))
	RAISERROR (@sError, 16, 1)
  END

--- Convert '' to NULL for @destination_db
IF LTRIM(RTRIM(@destination_db)) = ''
	SET	@destination_db = NULL

--- Determine if the database is in Simple Recovery
EXEC	@noarch = dbo.spIsDatabaseNoArchive @database_name

--- Define Temp Tables
DECLARE @backupset TABLE
(
	id							int					PRIMARY KEY,
	recovery_path_broken		bit,
	backup_set_id				int,
	db_backup_set_id			int,
	prev_tlog_backup_set_id		int,
	database_name				nvarchar(128),
	name						nvarchar(128),
	type						char(1),
	backup_start_date			datetime,
	has_bulk_logged_data		bit,
	backup_size					numeric(20,0),
	physical_device_name		nvarchar(260),
	file_exists					bit,
	database_backup_lsn			numeric(25,0),
	differential_base_lsn		numeric(25,0),
	checkpoint_lsn				numeric(25,0),
	fork_point_lsn				numeric(25,0),
	first_lsn					numeric(25,0),
	last_lsn					numeric(25,0),
	first_recovery_fork_guid	uniqueidentifier,
	last_recovery_fork_guid		uniqueidentifier
)

--- Determine the starting date based on the @last_backup_set_id requested
SELECT	@start_date = DATEADD(ss, 1, backup_start_date)
FROM	msdb.dbo.backupset
WHERE	database_name = @database_name
		AND backup_set_id = @last_backup_set_id

--- Get the full list of backups
INSERT INTO @backupset
	EXEC dbo.spRestoreGetAllBackups @database_name = @database_name, @files_must_exist = 0

--- Determine the criteria for selecting the proper backups to restore
SELECT		@db_id = ISNULL(t3.id, t2.id),
			@db_diff_id =  t2.id,
			@last_id = t1.id,
			@db_diff_backupsetid = t2.backup_set_id
FROM		@backupset t1
JOIN		@backupset t2 ON (t1.type <> 'L' AND t1.backup_set_id = t2.backup_set_id)
			OR (t1.type = 'L' AND t1.db_backup_set_id = t2.backup_set_id)
LEFT JOIN	@backupset t3 ON t2.db_backup_set_id = t3.backup_set_id
WHERE		t1.backup_set_id = @last_backup_set_id

--- Remove the backups that are not needed for this restore
DELETE
FROM		@backupset
WHERE		NOT (
				id IN (@db_id, @db_diff_id)
				OR (
					id BETWEEN @db_diff_id AND @last_id
					AND db_backup_set_id = @db_diff_backupsetid
				)
			)
			OR (
				type = 'L'
				AND db_backup_set_id IS NULL
			)

--- Return a resultset of the files needed to restore
SELECT		*
FROM		@backupset
ORDER BY	id

--- No backups have been taken
IF NOT EXISTS (SELECT TOP 1 * FROM @backupset)
  BEGIN
	--- Raise the error
	RAISERROR ('There is no backup history recorded to restore to the backupset.  The backup file location may need to be inventoried.', 16, 1)
  END

--- At least one backup file could not be found on disk
ELSE IF EXISTS (SELECT TOP 1 * FROM @backupset WHERE file_exists = 0)
  BEGIN
	--- Raise the error
	RAISERROR ('Not all files needed for restore have been found on disk.', 16, 1)
  END

ELSE
  BEGIN
	--- If requested, execute the restore commands
	IF @restore_now = 1
	  BEGIN
		--- Remove all snapshots
		SELECT	@sCommand = 'EXEC	dbo.spBackupSnapshotDrop @database_name = ''' + @database + ''''
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)

		--- Take a pre-restore backup
		IF @database <> @database_name AND ISNULL(@verify_only, 0) = 0
		  BEGIN
			--- Pick the shortest possible backup (if Simple Recovery then Diff, else Log)
			IF @noarch = 1
				SELECT	@sCommand = 'EXEC	dbo.spBackupDifferential @database_name = ''' + @database + ''', @label = ''PRE_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0'
			ELSE
				SELECT	@sCommand = 'EXEC	dbo.spBackupLog @database_name = ''' + @database + ''', @label = ''PRE_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0'

			--- Execute the command
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END
		ELSE IF ISNULL(@verify_only, 0) = 0
		  BEGIN
			--- Pick the shortest possible backup (if Simple Recovery then Diff, else Log)
			IF @noarch = 1
				SELECT	@sCommand = 'EXEC	dbo.spBackupDifferential @database_name = ''' + @database + ''', @label = ''PRE_Restore'', @verify = 0'
			ELSE
				SELECT	@sCommand = 'EXEC	dbo.spBackupLog @database_name = ''' + @database + ''', @label = ''PRE_Restore'', @verify = 0'

			--- Execute the command
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END

		--- Drop existing connections
		SELECT	@sCommand = 'EXEC	dbo.spRestoreKillProcesses @database_name = ''' + @database + ''''
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)

		--- Restore the backup file(s)
		DECLARE crsRestore CURSOR FOR 
			SELECT		id,
						(SELECT MIN(id) FROM @backupset) first_id,
						(SELECT MAX(id) FROM @backupset) last_id,
						physical_device_name
			FROM		@backupset
			ORDER BY	id
		OPEN crsRestore

		--- Get the first restore command and begin the restore process
		FETCH NEXT FROM crsRestore INTO @id, @first_id, @last_id, @physical_device_name
		WHILE @@FETCH_STATUS <> -1
		  BEGIN
			--- Execute one of the restore commands
			BEGIN TRY
				--- Modify parameters for the restore command
				SELECT	@replace = (CASE WHEN @id = @first_id THEN 1 ELSE 0 END),
						@recover = (CASE WHEN @id = @last_id THEN 1 ELSE 0 END)

				--- Execute the file restore
				EXEC	spRestoreBackupFile	@database_name = @database, 
											@file_name = @physical_device_name,
											@recover_now = @recover,
											@replace_existing = @replace,
											@verify_only = @verify_only,
											@stopat_time = @stopat_time
			END TRY

			--- If a command errors, exit the restore loop
			BEGIN CATCH
				--- Log the error - if possible
				SELECT @sResults = 'The database [' + @database + '] failed to restore the file (' + @physical_device_name + ').' 
				IF ERROR_NUMBER() IS NOT NULL
					SELECT @sResults = @sResults + CHAR(13)
									+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
									+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
									+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
									+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

				--- Print the error message to sysout
				PRINT @sResults

				--- Insert the error in the database
				INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
					VALUES (@database, GETDATE(), 'restore', 0, @sResults)

				--- Reraise the error to the caller
				RAISERROR (@sResults, 16, 1)

				BREAK
			END CATCH

			--- Move to the next restore command
			FETCH NEXT FROM crsRestore INTO @id, @first_id, @last_id, @physical_device_name
		  END
		CLOSE crsRestore
		DEALLOCATE crsRestore

		--- If the database is still in recovery, then try to recover the database
		IF EXISTS (SELECT TOP 1 * FROM master.sys.databases WHERE name = @database AND state = 1)
		  BEGIN
			SELECT	@sCommand = 'RESTORE DATABASE ' + QUOTENAME(@database) + ' WITH RECOVERY;'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END

		--- Remap Invalid Users Wherever Possible and Take a Backup
		IF @database <> @database_name AND ISNULL(@verify_only, 0) = 0
		  BEGIN
			--- Remap Invalid Users Wherever Possible
			SELECT	@sCommand = 'EXEC dbo.spRestoreChangeUsersLogin @database_name = ''' + @database + ''''
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
			
			--- Take a Backup
			SELECT	@sCommand = 'EXEC	dbo.spBackupDifferential @database_name = ''' + @database + ''', @label = ''POST_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)

			SELECT	@sCommand = 'EXEC	dbo.spBackupLog @database_name = ''' + @database + ''', @verify = 0'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END
		ELSE IF ISNULL(@verify_only, 0) = 0
		  BEGIN
			--- Take a Backup
			SELECT	@sCommand = 'EXEC	dbo.spBackupDifferential @database_name = ''' + @database + ''', @label = ''POST_Restore'', @verify = 0'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)

			SELECT	@sCommand = 'EXEC	dbo.spBackupLog @database_name = ''' + @database + ''', @verify = 0'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END

		--- Log the successful restore
		SELECT @sResults = 'The database [' + @database + '] was restored to the backup_set_id (' + CAST(@last_backup_set_id as varchar(100)) + ').' 
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sResults
		INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database, GETDATE(), 'restore', 1, @sResults)
	  END
  END

SET NOCOUNT OFF